import java.sql.*;
import java.util.ArrayList;
import java.util.Scanner;

public class MysqlBook {
    //case 1
    public static Book findBookById() throws SQLException, ClassNotFoundException {
        Book book = null;
        Scanner sc = new Scanner(System.in);
        int bNo;


        while (true) {
            System.out.println("请输入需要查询的书籍编号：(输入0返回上一级菜单！)");
            if (sc.hasNextInt()) {
                bNo = sc.nextInt();
                sc.nextLine();

                if (bNo == 0) {
                    return null;
                }

                if (bNo < 0) {
                    System.out.println("书籍编号输入错误，请输入大于0的数字编号！");
                    continue;
                }

                if (isBookExists(bNo)) {
                    break;
                } else {
                    System.out.println("该编号的书籍不存在，请重新输入！");
                }
            } else {
                System.out.println("书籍编号输入错误，请输入数字编号！");
                sc.nextLine();
            }
        }

        Class.forName("com.mysql.cj.jdbc.Driver");

        String url = "jdbc:mysql://localhost:3307/bookStore?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);

        Statement stmt = conn.createStatement();

        ResultSet res = stmt.executeQuery("SELECT * FROM t_book WHERE id = " + bNo);

        if (res.next()) {
            int id =res.getInt("id");
            String title = res.getString("title");
            String author = res.getString("author");
            double price = res.getDouble("price");

            book = new Book(id, title, author, price);
        }

        stmt.close();
        conn.close();

        return book;
    }

    //case2(case1和case3判断图书馆暂无书籍的时候也会用到findbBookList()方法)
    public static ArrayList<Book> findBookList() throws SQLException, ClassNotFoundException {
        ArrayList<Book> bookList = new ArrayList<>();
        Book book;

        Class.forName("com.mysql.cj.jdbc.Driver");

        String url = "jdbc:mysql://localhost:3307/bookStore?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);

        Statement stmt = conn.createStatement();

        ResultSet res = stmt.executeQuery("SELECT * FROM t_book");

        while (res.next()) {
            int id =res.getInt("id");
            String title = res.getString("title");
            String author = res.getString("author");
            double price = res.getDouble("price");

            book = new Book(id, title, author, price);

            bookList.add(book);
        }

        stmt.close();
        conn.close();

        return bookList;
    }

    //case 3
    public static int addBook() throws SQLException, ClassNotFoundException {
        Scanner sc = new Scanner(System.in);
        int id;
        while (true) {
            System.out.println("请输入书籍的编号：(输入0返回上一级菜单！)");
            if  (sc.hasNextInt()) {
                id = sc.nextInt();
                sc.nextLine();

                if (id == 0) {
                    return -1;
                }

                if (id < 0) {
                    System.out.println("编号输入错误，请输入大于0的数字编号！");
                }

                if (isBookExists(id)) {
                    System.out.println("该编号的书籍已存在，请重新输入！");
                } else  {
                    break;
                }
            } else {
                System.out.println("编号输入错误，请输入数字编号！");
            }
        }

        String title;
        while (true) {
            System.out.println("请输入书籍的名称：");
            title = sc.nextLine();
            if (title.isEmpty()) {
                System.out.println("书籍的名称不能为空，请重新输入！");
                sc.nextLine();
            } else   {
                break;
            }
        }

        String author ;
        while (true) {
            System.out.println("请输入书籍的作者：");
            author = sc.nextLine();
            if (author.isEmpty()) {
                System.out.println("书籍的作者不能为空，请重新输入！");
                sc.nextLine();
            } else   {
                break;
            }
        }

        double price;
        while (true) {
            System.out.println("请输入书籍的价格：");
            if (sc.hasNextDouble()) {
                price = sc.nextDouble();
                if (price <= 0) {
                    System.out.println("价格输入错误，请输入大于0的数字价格！");
                } else {
                    break;
                }
            } else {
                System.out.println("价格输入错误，请输入数字价格！");
            }
        }

        Class.forName("com.mysql.cj.jdbc.Driver");

        String url = "jdbc:mysql://localhost:3307/bookStore?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
        String username = "root";
        String password = "123456";
        try (Connection conn = DriverManager.getConnection(url, username, password);
             PreparedStatement sta = conn.prepareStatement("INSERT INTO t_book (id, title, author, price) VALUES (?, ?, ?, ?)")) {

            sta.setInt(1, id);
            sta.setString(2, title);
            sta.setString(3, author);
            sta.setDouble(4, price);

            return sta.executeUpdate();
        }
    }

    //case 4
    public static boolean deleteBook() throws SQLException, ClassNotFoundException {
        Scanner sc = new Scanner(System.in);
        int delId;

        while (true) {
            System.out.println("请输入需要下架书籍的编号：(输入0返回上一级菜单！)");
            if (sc.hasNextInt()) {
                delId = sc.nextInt();
                sc.nextLine();

                if (delId == 0) {
                    return false;
                }

                if (delId < 0) {
                    System.out.println("书籍编号输入错误，请输入大于0的数字编号！");
                    continue;
                }

                if (isBookExists(delId)) {
                    break;
                } else {
                    System.out.println("输入的书籍编号不存在，请重新输入！");
                }
            } else {
                System.out.println("书籍编号输入错误，请输入数字编号！");
                sc.nextLine();
            }
        }

        Class.forName("com.mysql.cj.jdbc.Driver");

        String url = "jdbc:mysql://localhost:3307/bookStore?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
        String username = "root";
        String password = "123456";

        Connection conn = DriverManager.getConnection(url, username, password);

        Statement stmt = conn.createStatement();

        stmt.executeUpdate("DELETE FROM t_book WHERE id = " + delId);

        stmt.close();
        conn.close();
        return true;
    }

    public static boolean isBookExists(int bNo) throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.cj.jdbc.Driver");

        String url = "jdbc:mysql://localhost:3307/bookStore?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
        String username = "root";
        String password = "123456";
        try (Connection conn = DriverManager.getConnection(url, username, password);
             PreparedStatement sta = conn.prepareStatement("SELECT * FROM t_book WHERE id = ?")) {
            sta.setInt(1, bNo);
            try (ResultSet res = sta.executeQuery()) {
                return res.next();
            }
        }
    }
}
